﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;

using System.Net;
using System.Net.Sockets;
using System.Threading;
using System.Net.Mail;

namespace ukeasbjWeb
{
    public partial class ukeas_bonus_100826 : System.Web.UI.Page
    {
        //从配置文件Web.Config 中读出数据库设置
        string strCnn = ConfigurationManager.AppSettings["glCnnStr"]; 




        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            //////只允许顾问、文案、OM、总部会计、Admin 访问
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_counsellor"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            if (!bIs_Counsellor && !bIs_Writer && !bIs_O_M && !bIs_Admin && !bIs_Accountant)
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }

            if (!IsPostBack)
            {	// 初始化页面

                //暂时关闭
                Bt_Bonus_Exel.Enabled = false;

                //////顾问、文案，设定姓名，关闭选项
                DDList_Counsellor_Writer.Enabled = true;
                DDList_Counsellor_Writer.Visible = true;
                if ((bIs_Counsellor || bIs_Writer))
                    if (!bIs_O_M)
                        if (!bIs_Admin)
                            if (!bIs_Accountant)
                            {
                                DDList_Counsellor_Writer.Enabled = false;
                                DDList_Counsellor_Writer.Visible = false;
                            }

                Init_Office_YearMonth();
                Init_CounsellorWriter();

            }

        }

        /// <summary>
        /// 顾问、文案控件的初始化
        /// </summary>
        private void Init_CounsellorWriter()
        {

            string sDDList_Office = Request["Office"];

            SqlConnection sqlCnn = new SqlConnection(strCnn);

            // 载入顾问、文案数据，绑定到 DDList_Counsellor_Writer 中
            string sSql = "select * from Employees ";
            sSql += "where C_N_Employee like '?' or ( ( Is_Counsellor = 1 or  Is_Writer = 1 ) and Office ='" + sDDList_Office + "' ) ";
            sSql += "order by Code_Employee asc";
            SqlDataAdapter sqlDA = new SqlDataAdapter(sSql, sqlCnn);
            sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet bsDataSet = new DataSet();
            sqlDA.Fill(bsDataSet, "Employees");
            DDList_Counsellor_Writer.DataSource = bsDataSet.Tables["Employees"];
            DDList_Counsellor_Writer.DataBind();
            sqlCnn.Close();

        }

        
        /// <summary>
        /// 地区、计奖年、月的初始化
        /// </summary>
        private void Init_Office_YearMonth()
        {
            TextBox_Year_Month.Text = Common_SXD.TimeToYM(DateTime.Now.ToString());
        }

        decimal J1_Jiang, J1_Kou, J5_Jiang, J5_Kou, J7_Jiang, J7_Kou, J2_Jiang, J2_Kou, J8_Jiang, J8_Kou, J3, J4, JJ1, JJ1Add_100826;
        int Max_TopPart = 0, Max_MiddlePart = 0, Max_BottomPart = 0;

        /// <summary>
        ///  初始化：计算奖金表，考虑'北京','上海','其他8个办公室'三种计算公式
        /// </summary>
        private void Init_Total_Bonus(string sTime)
        {
            ////////////////////////初始值为空
            Label_b1.Text = "";
            Label_b2.Text = "";

            Label_b3.Text = "";
            Label_b4.Text = "";

            Label_b5.Text = "";
            //Label_b6.Text = "";

            Label_b7.Text = "";
            Label_b8.Text = "";

            Label_Total_Not_US.Text = "";
            Label_Total.Text = "";
            Label_Total_US.Text = "";


            Label_Total_Bonus.Text = "";
            ///////////////////////			

            System.DateTime startDateTime20090925 =
                new System.DateTime(2009,        // Year
                9,            // Month
                25,            // Day
                0,            // Hour
                0,            // Minute
                0,            // Second
                0             // Millisecond
                );

            System.DateTime flagStartDateTime = Convert.ToDateTime(Common_SXD.strStartDate);

            JJ1 = J1_Jiang - J1_Kou;
            JJ1Add_100826 = JJ1 + J5_Jiang - J5_Kou;
            decimal b1 = 0, b2 = 0, b3 = 0, b4 = 0, b5 = 0;
            string strOffice = Request["Office"].ToString().Trim();

            if (strOffice == "北京" && flagStartDateTime <= startDateTime20090925)
            {
                //////////////北京（上海）有30000元指标的奖1
                if (JJ1Add_100826 < 0 && JJ1 < 0)
                    b1 = Math.Round(JJ1 * 0.05m * 0.333m, 2);
                else if (JJ1 >= 0 && JJ1Add_100826 < 30000)
                    b1 = 0;
                else if (JJ1Add_100826 >= 30000 && JJ1Add_100826 < 50000)
                    b1 = Math.Round(JJ1 * 0.05m * 0.333m, 2);
                else if (JJ1Add_100826 >= 50000)
                    b1 = Math.Round(JJ1 * 0.05m * 0.333m + JJ1 * 0.01m, 2);

                ///////////////北京奖9
                b3 = Math.Round(J3 * 0.05m * 0.2085m, 2);
                ///////////////北京奖10
                b4 = Math.Round(J4 * 0.05m * 0.2085m, 2);

            }
            else if (strOffice == "上海" || (strOffice == "北京" && flagStartDateTime > startDateTime20090925))
            {
                /////////////北京（上海）有30000元指标的奖1
                if (JJ1Add_100826 < 0 && JJ1 < 0)
                    b1 = JJ1 * 0.05m * 0.333m;
                else if (JJ1Add_100826 >= 0 && JJ1Add_100826 < 30000)
                    b1 = 0;
                else if (JJ1Add_100826 >= 30000 && JJ1Add_100826 < 50000)
                    b1 = JJ1 * 0.05m * 0.333m;
                else if (JJ1Add_100826 >= 50000)
                    b1 = JJ1 * 0.05m * 0.333m + JJ1 * 0.01m;
                b1 = Math.Round(b1, 2);

                ///////////////上海奖9
                b3 = Math.Round(J3 * 0.05m * 0.167m, 2);
                //////////////上海奖10
                b4 = Math.Round(J4 * 0.05m * 0.25m, 2);

            }
            else
            {
                ///////无30000元指标的奖1
                ///
                if (JJ1Add_100826 >= 50000)
                    b1 = JJ1 * 0.05m * 0.333m + JJ1 * 0.01m;
                else
                    b1 = JJ1 * 0.05m * 0.333m;
                b1 = Math.Round(b1, 2);

                ///////上海奖9
                b3 = Math.Round(J3 * 0.05m * 0.167m, 2);
                //////上海奖10
                b4 = Math.Round(J4 * 0.05m * 0.25m, 2);
            }

            ///////奖2
            b2 = Math.Round((J2_Jiang - J2_Kou) * 0.05m * 0.25m, 2);
            ///////奖6
            //b6 = Math.Round(J6 * 0.03m, 2);
            b5 = Math.Round((J5_Jiang - J5_Kou) * 0.02m, 2);

            Label_b1.Text = b1.ToString().Trim();
            Label_b2.Text = b2.ToString().Trim();

            Label_b3.Text = b3.ToString().Trim();
            Label_b4.Text = b4.ToString().Trim();

            Label_b5.Text = b5.ToString().Trim();
            //Label_b6.Text = b6.ToString().Trim();

            Label_b7.Text = (J7_Jiang - J7_Kou).ToString().Trim();
            Label_b8.Text = (J8_Jiang - J8_Kou).ToString().Trim();

            Label_Total_US.Text = (J5_Jiang - J5_Kou).ToString().Trim();
            Label_Total_Not_US.Text = JJ1.ToString().Trim();
            Label_Total.Text = (J1_Jiang - J1_Kou + J5_Jiang - J5_Kou).ToString().Trim();

            decimal total = b1 + b2 + b3 + b4 + b5 + (J7_Jiang - J7_Kou) + (J8_Jiang - J8_Kou);
            total = Math.Round(total, 2);
            Label_Total_Bonus.Text = total.ToString().Trim();

            ////////////////当月签约
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("JJ1"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(JJ1.ToString().Trim()));
                row.Cells.Add(cell3);

                //    Table_Bonus_1.Rows.Add(row);

            }
            ///////////////////////////////

        }

        protected void Bt_Refress_Search_Click(object sender, EventArgs e)
        {
            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            
            // 填写奖金计算网页
            //Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
            Init_Tables_Bonus1_Jiang();
            Init_Tables_Bonus1_Kou();

            Init_Tables_Bonus5_Jiang();
            Init_Tables_Bonus5_Kou();

            Init_Tables_Bonus2_Jiang();
            Init_Tables_Bonus2_Kou();
            Init_Tables_Bonus3();
            Init_Tables_Bonus4();
     //       Init_Tables_Bonus6();
            Init_Tables_Bonus7_Jiang();
            Init_Tables_Bonus7_Kou();
            Init_Tables_Bonus8_Jiang();
            Init_Tables_Bonus8_Kou();

            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year;
            Init_Total_Bonus(TextBox_Year_Month.Text);

            //string strOffice = Request["Office"].ToString().Trim();
            //string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            System.DateTime bonusDateTime20100801 =
    new System.DateTime(2010,        // Year
    8,            // Month
    1,            // Day
    0,            // Hour
    0,            // Minute
    0,            // Second
    0             // Millisecond
    );

            string temp = TextBox_Year_Month.Text + "-1";
            System.DateTime bonusNow = Convert.ToDateTime(temp);
            /// excel 奖金表的行数限制，分为上中下3个最大行数。
            if (bonusNow >= bonusDateTime20100801 && Max_TopPart <= 40 && Max_MiddlePart <= 16
                && Max_BottomPart <= 20)
            {/*
                Bt_Bonus_Exel.Enabled = true; //允许发送 excel格式的(100826版)奖金表

                SynchronousSocketClient MyClient = new SynchronousSocketClient();
                // 生成 exel 奖金文件
                MyClient.StartClient(strOffice, strCounsellor_Writer, Common_SXD.tempYear, Common_SXD.tempMonth, Response);
            */
            }
            else
            {
                Bt_Bonus_Exel.Enabled = false;
                Response.Write("无法生成 excel 格式的(100826版)奖金表（日期不合适或者数据超出表格范围）！");

            }
        }


        protected void Bt_Bonus_Exel_Click(object sender, EventArgs e)
        {
            Sendmail_Bonus_Exel();
        }

        /// <summary>
        ///  初始化奖金1奖，顾问收费生签约奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 只有一期款发生在本月， 二期款发生在其它月份 
        /// </newpara>
        /// <newpara> 
        /// B) 只有二期款发生在本月， 一期款发生在其他月份 
        /// </newpara>
        /// <newpara> 
        /// C) 二期款、 一期款均发生在本月
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus1_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus1_Jiang.Rows.Clear();
            //			Table_Bonus1_Kou.Rows.Clear();
            J1_Jiang = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select  Student_Name, Student_Type, First_Payment as 'Shishoujinge'  from contract where ";

            string strFilter = "";
            /// <newpara> 
            /// A) 只有一期款发生在本月， 二期款发生在其它月份
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ((Date_second_payment < '" + Common_SXD.strStartDate + "') OR (Date_second_payment > '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  Student_Type like '%联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND counsellor = '" + strCounsellor_Writer + "'";

            string sOrder = "";
            sOrder = " order by " + "Series_No DESC";


            sqlCmd.CommandText = sSql + strFilter + sOrder;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus1_Jiang.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());

                }
                MyLines = MyLines + 1;
                Table_Bonus1_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select  Student_Name, Student_Type, Second_Payment   from contract where ";

            /// <newpara> 
            /// B) 二期款发生在本月， 一期款发生在其他月份 
            /// </newpara>
            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment < '" + Common_SXD.strStartDate + "') OR (Date_First_payment > '" + Common_SXD.strEndingDate;
            strFilter += "'))  AND Destinations_OS not like '美国' AND ( Student_Type like '全程一次' OR  Student_Type like '%联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);		

            sqlCmd.CommandText = sSql + strFilter;

            //			SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;
            
            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;

                Table_Bonus1_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment   from contract where ";

            /// <newpara> 
            /// C) 二期款、 一期款均发生在本月
            /// </newpara>
            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment >= '" + Common_SXD.strStartDate + "') AND (Date_First_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  Student_Type like '%联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            //			SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;
            
            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;

                Table_Bonus1_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ///<newpara>
            /// 生成奖1的小计	
            ///</newpara>
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J1_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus1_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
               Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金5奖，顾问单美合同
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 只有一期款发生在本月， 二期款发生在其它月份 
        /// </newpara>
        /// <newpara> 
        /// B) 只有二期款发生在本月， 一期款发生在其他月份 
        /// </newpara>
        /// <newpara> 
        /// C) 二期款、 一期款均发生在本月
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus5_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus5_Jiang.Rows.Clear();
            //			Table_Bonus1_Kou.Rows.Clear();
            J5_Jiang = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select  Student_Name, Student_Type, First_Payment as 'Shishoujinge'  from contract where ";

            string strFilter = "";
            /// <newpara> 
            /// A) 只有一期款发生在本月， 二期款发生在其它月份
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ((Date_second_payment < '" + Common_SXD.strStartDate + "') OR (Date_second_payment > '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '美国单文书' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND counsellor = '" + strCounsellor_Writer + "'";

            string sOrder = "";
            sOrder = " order by " + "Series_No DESC";


            sqlCmd.CommandText = sSql + strFilter + sOrder;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus5_Jiang.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());

                }
                MyLines = MyLines + 1;
                Table_Bonus5_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select  Student_Name, Student_Type, Second_Payment   from contract where ";

            /// <newpara> 
            /// B) 二期款发生在本月， 一期款发生在其他月份 
            /// </newpara>
            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment < '" + Common_SXD.strStartDate + "') OR (Date_First_payment > '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '美国单文书' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);		

            sqlCmd.CommandText = sSql + strFilter;

            //			SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus5_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment   from contract where ";

            /// <newpara> 
            /// C) 二期款、 一期款均发生在本月
            /// </newpara>
            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment >= '" + Common_SXD.strStartDate + "') AND (Date_First_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '美国单文书' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            //			SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus5_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ///<newpara>
            /// 生成奖1的小计	
            ///</newpara>
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J5_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus5_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_MiddlePart)
                Max_MiddlePart = MyLines;

        }

        /// <summary>
        ///  初始化奖金1扣奖，顾问收费生退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次', '%联办'，'全程二次', '申请学校', '签证'的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus1_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus1_Kou.Rows.Clear();
            //			Table_Bonus1_Kou.Rows.Clear();
            J1_Kou = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象


            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次', '%联办'，'全程二次', '申请学校', '签证'的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '全程二次' OR  Student_Type like '%联办'  OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus1_Kou.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1_Kou += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus1_Kou.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J1_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus1_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金5扣奖，顾问单美合同退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '单美合同'的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus5_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus5_Kou.Rows.Clear();
            //			Table_Bonus5_Kou.Rows.Clear();
            J5_Kou = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象


            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次', '美国单文书' ，'全程二次', '申请学校', '签证'的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
            /// </newpara>
            //strFilter += "Office like '" + strOffice +"' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS like '美国' AND ( Student_Type = '全程一次' OR  Student_Type = '美国单文书' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "' AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus5_Kou.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5_Kou += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus5_Kou.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J5_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus5_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_MiddlePart)
                Max_MiddlePart = MyLines;

        }

        /// <summary>
        ///  初始化奖金2，文案收费生完成Offer/Visa奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次'， '%联办'（不含英美联办），'申请学校'offer完成奖
        /// </newpara>
        /// <newpara>
        /// B) '全程二次'的一期款， 文案offer完成奖。
        /// </newpara>
        /// <newpara>
        /// C) '全程二次'的二期款，visa完成奖。
        /// </newpara>
        /// <newpara>
        /// D) '签证'，visa完成奖。
        /// </newpara>
        /// <newpara>
        /// E) '英美联办'， 英国文案的奖2。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus2_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus2_Jiang.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J2_Jiang = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次'， '%联办'（不含英美联办），'申请学校'offer完成奖
            /// </newpara>
            strFilter += "((Date_Completion_Offer >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR ( Student_Type like '%联办' AND Destinations_OS not like '英美' ) OR Student_Type = '申请学校' )  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus2_Jiang.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Jiang.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////////


//            sqlCnn = new SqlConnection();
//            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// B) '全程二次'的一期款， 文案offer完成奖。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Offer >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND  Student_Type = '全程二次'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Jiang.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////////////////////

            //sqlCnn = new SqlConnection();
            //sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //strOffice = Request["Office"].ToString().Trim();

            //if (DDList_Counsellor_Writer.Visible)
            //{
            //    strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            //}
            //else
            //    strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";
            sSql = "select  Student_Name, Student_Type, Second_Payment as 'Shishoujinge' from contract where ";

            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// C) '全程二次'的第2次付费, visa完成
            /// </newpara>
            strFilter += "(Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";

            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND  Student_Type = '全程二次'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Jiang.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ///////////////////////////////////////////////////////
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "";
            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// D) '签证'，visa完成。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type like '%签证')  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ///////////////////////////////////////////////////////
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "";
            sSql = "select  Student_Name, Student_Type, '12000.00' as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// E) '英美联办'， 英国文案的奖2。
            /// </newpara>
            strFilter += "((Date_Completion_Offer >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Student_Type like '%联办' AND Destinations_OS like '英美' AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J2_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus2_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;
        }

        /// <summary>
        ///  初始化奖金2扣, 文案收费生Offer/Visa退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次'，'%联办'（不含英美联办），'申请学校'，offer退约。
        /// </newpara>
        /// <newpara>
        /// B) '全程二次'的 offer 完成退约（第二期合同没有执行）。
        /// </newpara>
        /// <newpara>
        /// C) 英美联办退款。退款金额 > (实收 - 12000元)，英国文案扣奖，
        /// 扣奖的合同计算额 = 退款金额 - (实收 - 12000元)。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus2_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus2_Kou.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J2_Kou = 0;
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象


            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select  Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次'，'%联办'（不含英美联办），'申请学校'，offer退约。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  ( Student_Type like '%联办' AND Destinations_OS not like '英美' ) OR Student_Type = '申请学校' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus2_Kou.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2_Kou += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Kou.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ////////////////////////////////////////////////////////////////////////////////////////////////////////////////

            //sqlCnn = new SqlConnection();
            //sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象	

            sSql = "select  Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// B) '全程二次'的 offer 完成退约（第二期合同没有执行）。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Second_Payment = 0 AND Date_Completion_Offer > '1930-1-1' AND Destinations_OS not like '美国' AND ( Student_Type = '全程二次' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Kou.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象	

            sSql = "select  Student_Name, Student_Type, (Termination_Refund - First_Payment - Second_Payment + 12000) as 'Shituijinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// C) 英美联办退款。退款金额 > (实收 - 12000元)，英国文案扣奖，
            /// 扣奖的合同计算额 = 退款金额 - (实收 - 12000元)。
            /// </newpara>
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND ( Student_Type like '%联办' AND Destinations_OS like '英美' ) AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund >  First_Payment + Second_Payment - 12000 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                }
                MyLines = MyLines + 1;
                Table_Bonus2_Kou.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J2_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus2_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金表3，顾问结案奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// （不含英美联办），不含美国项目
        /// </newpara>
        /// <newpara>
        /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
        /// （不含英美联办），不含美国项目
        /// </newpara>
        /// <newpara>
        /// C)顾问结案奖，英美联办，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// </newpara>
        /// <newpara>
        /// D) 顾问结案奖，英美联办，净收入（一期款 - 退款） 为结案的金额
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus3()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus3.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J3 = 0;
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// （不含英美联办），不含美国项目
            /// </newpara>
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  ( Student_Type like '%联办' AND Destinations_OS not like '英美' ) OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus3.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J3 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus3.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串
            sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
            /// （不含英美联办），不含美国项目
            /// </newpara>
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment < '1930-1-1') AND ";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  ( Student_Type like '%联办' AND Destinations_OS not like '英美' ) OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            ///SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;


            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J3 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus3.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串
            sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// C)顾问结案奖，英美联办，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Notes like '%去英国%' AND  Student_Type like '%联办' AND Destinations_OS like '英美' AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            ///SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;


            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J3 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus3.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串
            sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// D) 顾问结案奖，英美联办，净收入（一期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment < '1930-1-1') AND ";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Notes like '%去英国%' AND  Student_Type like '%联办' AND Destinations_OS like '英美' AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            ///SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;


            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J3 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus3.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J3.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus3.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金4，文案结案奖 
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 文案结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// （不含英美联办），不含美国项目
        /// </newpara>
        /// <newpara>
        /// B) 文案结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
        /// （不含英美联办），不含美国项目
        /// </newpara>
        /// <newpara>
        /// C) 文案结案奖，英美联办，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// </newpara>
        /// <newpara>
        /// D) 文案结案奖，英美联办，净收入（一期款 - 退款） 为结案的金额
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus4()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus4.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J4 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "", strFilter;

            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
            
            /// <newpara>
            /// A) 文案结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// （不含英美联办），不含美国项目
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  ( Student_Type like '%联办' AND Destinations_OS not like '英美' ) OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND Writer = '" + strCounsellor_Writer + "'";

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus4.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J4 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus4.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接


            //sqlCnn = new SqlConnection();
            //sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串
            sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
            //sSql += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// B) 顾问结案奖，4种合同，净收入（一期款 - 退款） 为结案的金额
            /// （不含英美联办），不含美国项目
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment < '1930-1-1') AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Destinations_OS not like '美国' AND ( Student_Type = '全程一次' OR  ( Student_Type like '%联办' AND Destinations_OS not like '英美' ) OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type like '%签证')  AND Writer = '" + strCounsellor_Writer + "'";

            sqlCmd.CommandText = sSql + strFilter;

            //SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J4 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus4.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串

            sSql = "select  Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
            /// <newpara>
            /// C) 文案结案奖，英美联办，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Notes like '%去英国%' AND  Student_Type like '%联办' AND Destinations_OS like '英美' AND Writer = '" + strCounsellor_Writer + "'";
            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J4 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus4.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串
            sSql = "";

            sSql = "select  Student_Name, Student_Type, First_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
            /// <newpara>
            /// D) 文案结案奖，英美联办，净收入（一期款 - 退款） 为结案的金额
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract )";
            strFilter += " AND (Date_second_payment < '1930-1-1') AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Notes like '%去英国%' AND  Student_Type like '%联办' AND Destinations_OS like '英美' AND Writer = '" + strCounsellor_Writer + "'";

            sqlCmd.CommandText = sSql + strFilter;

            //SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J4 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus4.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J4.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus4.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金7奖，顾问零中介签约奖。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 本月，只有第一次付款发生在本月， First_Payment大于等于1000
        /// </newpara>
        /// <newpara>
        /// B) 本月，发生第二次付款， 第一次付款小于1000, 2次款之和大于等于1000。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus7_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus7_Jiang.Rows.Clear();

            J7_Jiang = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, First_Payment as 'Yajing', 50 as 'Bonus'  from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 本月，只有第一次付款发生在本月， First_Payment >=1000
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_second_payment < '" + Common_SXD.strStartDate + "') OR (Date_second_payment > '" + Common_SXD.strEndingDate + "'))  AND First_Payment >= 1000 AND Student_Type = '零中介'  AND counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus7_Jiang.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J7_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus7_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            /////////////////////////////////////////////////////////

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select  Student_Name, First_Payment + Second_Payment as 'Yajing', 50 as 'Bonus'  from contract where ";

            /// <newpara>
            /// B) 考虑第二次付款发生在本月， 第一次付款小于1000, 2次款之和大于等于1000。
            /// </newpara>
            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND First_Payment < 1000 AND First_Payment + Second_Payment >= 1000   AND Student_Type = '零中介'  AND counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J7_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus7_Jiang.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J7_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus7_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_BottomPart)
                Max_BottomPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金7扣奖，顾问零中介退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 本月发生的零中介退款，需要扣奖的合同。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus7_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus7_Kou.Rows.Clear();
            J7_Kou = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, 50  as 'Koukuan' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 本月发生的零中介退款，需要扣奖的合同。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Termination_Refund >= '" + Common_SXD.strStartDate + "' AND  Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "' ) AND Termination_Refund<>0 AND (Notes like '%deduct%' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus7_Kou.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J7_Kou += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus7_Kou.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                //                HtmlTableCell cell = new HtmlTableCell();
                //                cell.Controls.Add(new LiteralControl(" "));
                //                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);


                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J7_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus7_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_BottomPart)
                Max_BottomPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金8奖，文案零中介完成Offer奖。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 文案零中介完成Offer奖， 收到第一个offer，(First_Payment + Second_Payment) >=1000
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus8_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus8_Jiang.Rows.Clear();
            J8_Jiang = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select  Student_Name, First_Payment + Second_Payment as 'Yajing', 50  as 'Bonus' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 文案零中介完成Offer奖， 收到第一个offer，(First_Payment + Second_Payment) >=1000
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Completion_Offer >= '" + Common_SXD.strStartDate + "' AND  Date_Completion_Offer <= '" + Common_SXD.strEndingDate + "' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus8_Jiang.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J8_Jiang += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus8_Jiang.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J8_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus8_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_BottomPart)
                Max_BottomPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金8扣奖，文案零中介Offer退约。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 文案零中介Offer退约，需要扣奖的合同，已经领取奖金，First_Payment >=1000
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus8_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus8_Kou.Rows.Clear();
            J8_Kou = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select  Student_Name, 50 as 'Koukuan' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 文案零中介Offer退约，需要扣奖的合同，已经领取奖金，(First_Payment + Second_Payment) >=1000
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Termination_Refund >= '" + Common_SXD.strStartDate + "' AND  Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "' ) AND Termination_Refund > 0 AND Date_Completion_Offer > '1930-1-1' AND (Notes like '%deduct%' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus8_Kou.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J8_Kou += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }
                MyLines = MyLines + 1;
                Table_Bonus8_Kou.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                //               HtmlTableCell cell = new HtmlTableCell();
                //               cell.Controls.Add(new LiteralControl(" "));
                //               row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J8_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus8_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_BottomPart)
                Max_BottomPart = MyLines;

        }

        public class SynchronousSocketClient
        {

            public bool StartClient(string strOffice, string strCounsellor_Writer, string tempYear, string tempMonth, HttpResponse myResponse)
            {
                // Data buffer for incoming data.
                byte[] bytes = new byte[1024];

                // Connect to a remote device.
                try
                {
                    // Establish the remote endpoint for the socket.
                    // The name of the
                    // remote device is "host.contoso.com".
                    //				IPHostEntry ipHostInfo = Dns.Resolve("localhost");
                    IPHostEntry ipHostInfo = Dns.GetHostEntry("localhost");
                    IPAddress ipAddress = ipHostInfo.AddressList[0];
                    IPEndPoint remoteEP = new IPEndPoint(ipAddress, 10086);

                    // Create a TCP/IP  socket.
                    Socket sender = new Socket(AddressFamily.InterNetwork,
                        SocketType.Stream, ProtocolType.Tcp);

                    // Connect the socket to the remote endpoint. Catch any errors.
                    try
                    {
                        sender.Connect(remoteEP);

                        Console.WriteLine("Socket connected to {0}",
                            sender.RemoteEndPoint.ToString());

                        // Encode the data string into a byte array.
                        byte[] msg = Encoding.Default.GetBytes("MakeBonus3In1 " + strOffice + " " + strCounsellor_Writer + " " + tempYear + " " + tempMonth + "  \r\n");

                        // Send the data through the socket.
                        int bytesSent = sender.Send(msg);

                        // Receive the response from the remote device.
                        int bytesRec = sender.Receive(bytes);
                        Console.WriteLine("Echoed test = {0}",
                            Encoding.Default.GetString(bytes, 0, bytesRec));

                        // Release the socket.
                        sender.Shutdown(SocketShutdown.Both);
                        sender.Close();

                    }
                    catch (ArgumentNullException ane)
                    {
                        Console.WriteLine("ArgumentNullException : {0}", ane.ToString());
                        return false;
                    }
                    catch (SocketException se)
                    {
                        Console.WriteLine("SocketException : {0}", se.ToString());
                        return false;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Unexpected exception : {0}", e.ToString());
                        return false;
                    }

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    return false;
                }
                return true;
            }

        }

        /// <summary>
        /// 发送奖金表到您的邮箱
        /// </summary>
        private void Sendmail_Bonus_Exel()
        {
            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            /* 注释后，不生成奖金文件
            SynchronousSocketClient MyClient = new SynchronousSocketClient();

            MyClient.StartClient(strOffice, strCounsellor_Writer, Common_SXD.tempYear, Common_SXD.tempMonth, Response); //			StartClient();

            Thread.Sleep(9000);
            */
            try
            {
                // 服务器名，端口号
                System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(Common_SXD.smtp_ukeas, 25);
                smtp.UseDefaultCredentials = false;
                //账户，密码
                smtp.Credentials = new System.Net.NetworkCredential("jianwei_rong@promisingEDU.com", Common_SXD.pwd_jianwei_rong);
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;

                System.Net.Mail.MailMessage myMsg = new System.Net.Mail.MailMessage();
                //发件人
                myMsg.From = new MailAddress("jianwei_rong@promisingEDU.com", "戎建伟", Encoding.GetEncoding("GB2312"));

                myMsg.SubjectEncoding = Encoding.GetEncoding("GB2312");
                myMsg.BodyEncoding = Encoding.GetEncoding("GB2312");
                myMsg.Subject = strOffice + " " + strCounsellor_Writer + "奖金表";
                myMsg.Body = "本邮件是数据库的(100826版)奖金计算结果，请不要回复邮件。";


                string strEmail = Session["Email"].ToString().Trim();
                string sAddress_Email = Common_SXD.eMail_Replacement(strEmail); ///strEmail + "@promisingEDU.com";
                //收件人
                myMsg.To.Add(new MailAddress(sAddress_Email, "", Encoding.GetEncoding("GB2312")));
                //密送
                ///myMsg.Bcc.Add(new MailAddress("rongjianwei@sina.com", "", Encoding.GetEncoding("GB2312")));

                Response.Charset = "gb2312";

                //读取当前目录的path
                //				string strFilePath = Directory.GetCurrentDirectory();
                //读取服务器物理目录
                string strFilePath = Request.PhysicalApplicationPath;
                Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
                string file = strFilePath + @"..\..\Model_Bonus\" + strOffice + "_" +
                    strCounsellor_Writer + Common_SXD.tempDate + "(100826版)奖金申报表" + ".xls";

                //添加附件
                System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(file);
                myMsg.Attachments.Add(attachment);

                myMsg.Priority = System.Net.Mail.MailPriority.Low;

                smtp.Send(myMsg);
                
                myMsg.Attachments.Remove(attachment);

                attachment.Dispose();

                Response.Clear();
                Response.Write("<li>(100826版)奖金表已经发送！</li>");
                //               MessageBox MyB = new MessageBox(this);
                //               MyB.Show("奖金表已经发送！");

            }
            catch (System.Net.Mail.SmtpException se)
            {
                Response.Write("<li>smtp error.</li>" + se.Message);
            }
            catch (FormatException se)
            {
                Response.Write("<li>format error.</li>" + se.Message);
            }
            catch (System.Exception e)
            {
                Response.Write("<li>system error.</li>" + e.Message + "; Target: " + e.TargetSite);
            }

        }


    }
}
